﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RRHH.Entities;
using System.Data.SqlClient;
using System.Data;

namespace RRHH.DataAccess
{
    public class DAOMaestroPersonal2
    {

        /************************************************************************************************
        *                          J. SALCEDO MAESTROPERSONAL
        * **********************************************************************************************/

        public List<BEMaestroPersonal> ListarMaestroPersonalXIDPersonal(int personalID)
        {

            string sql = "SP_LISTAR_MAESTROPERSONAL_X_IDPERSONAL";

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int).Value = personalID;

            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.StoredProcedure;

            using (DataAccess adoDataService = new DataAccess())
            {
                List<BEMaestroPersonal> _BLMaestroPersonal = new List<BEMaestroPersonal>();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                while (dr.Read())
                {
                    BEMaestroPersonal _BEMaestroPersonal = new BEMaestroPersonal();
                    _BEMaestroPersonal.CODIGOEMPLEADO = Convert.ToString(dr["CodigoEmpleado"]);
                    _BEMaestroPersonal.idPersonal.IDPERSONAL = Convert.ToInt32(dr["IDPERSONAL"]);
                    _BEMaestroPersonal.idEmpresa.idAuto = Convert.ToInt32(dr["IDEMPRESA"]);
                    _BEMaestroPersonal.FLAGACTIVO = Convert.ToString(dr["FLAGACTIVO"]);
                    _BEMaestroPersonal.ESTADO = Convert.ToString(dr["ESTADO"]);
                    _BEMaestroPersonal.FECHAINGRESO = Convert.ToDateTime(dr["FECHAINGRESO"]);
                    _BEMaestroPersonal.idEmpresa.nombreEmpresa = Convert.ToString(dr["NOMBREEMPRESA"]);
                    _BEMaestroPersonal.FECHACESE = Convert.ToDateTime(dr["FECHACESE"]);

                    _BLMaestroPersonal.Add(_BEMaestroPersonal);
                }
                dr.Close();

                adoDataService.Dispose();

                return _BLMaestroPersonal;
            }

        }

        public BEMaestroPersonal BuscarMaestroPersonalXID(int personalID, string codigoEmpleado)
        {

            string sql = "SP_BUSCAR_MAESTROPERSONAL_X_ID";

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int).Value = personalID;
            sqlCommand.Parameters.Add("@CodigoEmpleado", SqlDbType.VarChar).Value = codigoEmpleado;

            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.StoredProcedure;

            using (DataAccess adoDataService = new DataAccess())
            {
                BEMaestroPersonal _BEMaestroPersonal = new BEMaestroPersonal();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                if (dr.Read())
                {
                    _BEMaestroPersonal.CODIGOEMPLEADO = Convert.ToString(dr["CodigoEmpleado"]);
                    _BEMaestroPersonal.idPersonal.IDPERSONAL = Convert.ToInt32(dr["IDPERSONAL"]);
                    _BEMaestroPersonal.codigoClasePlanilla.codigoClasePlanilla = Convert.ToInt32(dr["CODIGOCLASEPLANILLA"]);
                    _BEMaestroPersonal.idEmpresa.idAuto = Convert.ToInt32(dr["IDEMPRESA"]);
                    _BEMaestroPersonal.FLAGACTIVO = Convert.ToString(dr["FLAGACTIVO"]);
                    _BEMaestroPersonal.ESTADO = Convert.ToString(dr["ESTADO"]);
                    _BEMaestroPersonal.CARGAFAMILIAR = Convert.ToString(dr["CARGAFAMILIAR"]);
                    _BEMaestroPersonal.NROCUENTABANCO = Convert.ToString(dr["NROCUENTABANCO"]);
                    _BEMaestroPersonal.FECHAINGRESO = Convert.ToDateTime(dr["FECHAINGRESO"]);
                    _BEMaestroPersonal.idTIpoCuenta.idAuto = Convert.ToInt32(dr["IDTIPOCUENTA"]);
                    _BEMaestroPersonal.idEps.idAuto = Convert.ToInt32(dr["IDEPS"]);
                    _BEMaestroPersonal.codigoCentroCosto.idCentroCosto = Convert.ToInt32(dr["CODIGOCENTROCOSTO"]);
                    _BEMaestroPersonal.idCategoriaEmpleado.idAuto = Convert.ToInt32(dr["IDCATEGORIASEMPLEADO"]);
                    _BEMaestroPersonal.idBanco.idAuto = Convert.ToInt32(dr["IDBANCO"]);
                    _BEMaestroPersonal.idArea.idAuto = Convert.ToInt32(dr["IDAREA"]);
                    _BEMaestroPersonal.idCargo.idAuto = Convert.ToInt32(dr["IDCARGO"]);  
                }
                dr.Close();
                adoDataService.Dispose();
                return _BEMaestroPersonal;
            }
        }


        public int RegistrarMaestroPersonal(BEMaestroPersonal _BEMaestroPersonal)
        {
            try
            {

                SqlCommand sqlCommand = new SqlCommand("SP_REGISTRAR_MAESTROPERSONAL");
                sqlCommand.CommandType = CommandType.StoredProcedure;

                sqlCommand.Parameters.Add(new SqlParameter("@IDPERSONAL", _BEMaestroPersonal.idPersonal.IDPERSONAL));
                sqlCommand.Parameters.Add(new SqlParameter("@CODIGOCLASEPLANILLA", _BEMaestroPersonal.codigoClasePlanilla.codigoClasePlanilla));

                sqlCommand.Parameters.Add(new SqlParameter("@CARGAFAMILIAR", _BEMaestroPersonal.CARGAFAMILIAR));
                sqlCommand.Parameters.Add(new SqlParameter("@NROCUENTABANCO", _BEMaestroPersonal.NROCUENTABANCO));

                sqlCommand.Parameters.Add(new SqlParameter("@FECHAINGRESO", _BEMaestroPersonal.FECHAINGRESO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDTIPOCUENTA", _BEMaestroPersonal.idTIpoCuenta.idAuto));

                sqlCommand.Parameters.Add(new SqlParameter("@IDEPS", _BEMaestroPersonal.idEps.idAuto));
                sqlCommand.Parameters.Add(new SqlParameter("@CODIGOCENTROCOSTO", _BEMaestroPersonal.codigoCentroCosto.idCentroCosto));

                sqlCommand.Parameters.Add(new SqlParameter("@IDEMPRESA", _BEMaestroPersonal.idEmpresa.idAuto));
                sqlCommand.Parameters.Add(new SqlParameter("@IDCATEGORIASEMPLEADO", _BEMaestroPersonal.idCategoriaEmpleado.idAuto));

                sqlCommand.Parameters.Add(new SqlParameter("@IDBANCO", _BEMaestroPersonal.idBanco.idAuto));
                sqlCommand.Parameters.Add(new SqlParameter("@IDAREA", _BEMaestroPersonal.idArea.idAuto));

                sqlCommand.Parameters.Add(new SqlParameter("@IDCARGO", _BEMaestroPersonal.idCargo.idAuto));

                using (DataAccess adoDataService = new DataAccess())
                {
                    adoDataService.BeginTransaction();
                    //Int32 orderID = adoDataService.ExecuteScalar(sqlCommand);
                    adoDataService.ExecuteNonQuery(sqlCommand);
                    adoDataService.CommitTransaction();
                    adoDataService.Dispose();

                    //oPersonal.IDPERSONAL = orderID;
                }
            }
            catch (Exception ex)
            {

            }
            return 1;
        }

        public int ModificarMaestroPersonal(BEMaestroPersonal _BEMaestroPersonal)
        {
            try
            {
                SqlCommand sqlCommand = new SqlCommand("SP_MODIFICAR_MAESTROPERSONAL");
                sqlCommand.CommandType = CommandType.StoredProcedure;

                sqlCommand.Parameters.Add(new SqlParameter("@IDPERSONAL", _BEMaestroPersonal.idPersonal.IDPERSONAL));
                sqlCommand.Parameters.Add(new SqlParameter("@CodigoEmpleado", _BEMaestroPersonal.CODIGOEMPLEADO));

                sqlCommand.Parameters.Add(new SqlParameter("@CARGAFAMILIAR", _BEMaestroPersonal.CARGAFAMILIAR));
                sqlCommand.Parameters.Add(new SqlParameter("@NROCUENTABANCO", _BEMaestroPersonal.NROCUENTABANCO));

                sqlCommand.Parameters.Add(new SqlParameter("@FECHAINGRESO", _BEMaestroPersonal.FECHAINGRESO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDTIPOCUENTA", _BEMaestroPersonal.idTIpoCuenta.idAuto));

                sqlCommand.Parameters.Add(new SqlParameter("@IDEPS", _BEMaestroPersonal.idEps.idAuto));
                sqlCommand.Parameters.Add(new SqlParameter("@CODIGOCENTROCOSTO", _BEMaestroPersonal.codigoCentroCosto.idCentroCosto));

                sqlCommand.Parameters.Add(new SqlParameter("@IDCATEGORIASEMPLEADO", _BEMaestroPersonal.idCategoriaEmpleado.idAuto));
                sqlCommand.Parameters.Add(new SqlParameter("@IDBANCO", _BEMaestroPersonal.idBanco.idAuto));

                sqlCommand.Parameters.Add(new SqlParameter("@IDAREA", _BEMaestroPersonal.idArea.idAuto));
                sqlCommand.Parameters.Add(new SqlParameter("@IDCARGO", _BEMaestroPersonal.idCargo.idAuto));

                using (DataAccess adoDataService = new DataAccess())
                {
                    adoDataService.BeginTransaction();
                    //Int32 orderID = adoDataService.ExecuteScalar(sqlCommand);
                    adoDataService.ExecuteNonQuery(sqlCommand);
                    adoDataService.CommitTransaction();
                    adoDataService.Dispose();

                    //oPersonal.IDPERSONAL = orderID;
                }

            }
            catch (Exception ex)
            {

            }
            return 1;
        }



        /************************************************************************************************
         *                          CARLOS ENVIO T-REGISTRO
         * **********************************************************************************************/

        public List<BEMaestroPersonal> ListarIngresosPersonal()
        {

            string sql = "SP_LISTAR_INGRESOS_PENDIENTES_TREGISTRO";

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = sql;

            using (DataAccess adoDataService = new DataAccess())
            {

                List<BEMaestroPersonal> _BLMaestroPersonal = new List<BEMaestroPersonal>();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                while (dr.Read())
                {
                    BEMaestroPersonal _BEMaestroPersonal = new BEMaestroPersonal();
                    _BEMaestroPersonal.CODIGOEMPLEADO = Convert.ToString(dr["CODIGOEMPLEADO"]);
                    _BEMaestroPersonal.idPersonal.IDPERSONAL = Convert.ToInt32(dr["IDPERSONAL"]);
                    _BEMaestroPersonal.idPersonal.NUMERODOCUMENTO = Convert.ToString(dr["NUMERODOCUMENTO"]);
                    _BEMaestroPersonal.idPersonal.APEPATERNOEMPLEADO = Convert.ToString(dr["nombres"]);
                    _BEMaestroPersonal.FECHAINGRESO = Convert.ToDateTime(dr["FECHAINGRESO"]);

                    _BLMaestroPersonal.Add(_BEMaestroPersonal);
                }
                dr.Close();
                adoDataService.Dispose();
                return _BLMaestroPersonal;
            }

        }
        
        public BEIngresoTRegistro EnvioIngresoTRegistro(int personalID,string codigoEmpleado)
        {
            
            string sql = "SP_ENVIAR_INGRESO_TREGISTRO";

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int);
            sqlCommand.Parameters["@IDPERSONAL"].Value = personalID;
            sqlCommand.Parameters.Add("@cod_empleado", SqlDbType.VarChar).Value = codigoEmpleado;

            sqlCommand .CommandText= sql;
            sqlCommand.CommandType = CommandType.StoredProcedure;

            using (DataAccess adoDataService = new DataAccess())
            {
                BEIngresoTRegistro _BEIngresoTRegistro = new BEIngresoTRegistro();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                if (dr.Read())
                {
                    _BEIngresoTRegistro.NUMERODOCUMENTO = Convert.ToString(dr["NUMERODOCUMENTO"]);
                    _BEIngresoTRegistro.IDDOCIDENTIDAD = Convert.ToInt32(dr["IDDOCIDENTIDAD"]);
                    _BEIngresoTRegistro.IDDEPDOMICILIO = Convert.ToInt32(dr["IDDEPDOMICILIO"]);
                    _BEIngresoTRegistro.IDPROVDOMICILIO = Convert.ToInt32(dr["IDPROVDOMICILIO"]);
                    _BEIngresoTRegistro.IDDISTDOMICILIO = Convert.ToInt32(dr["IDDISTDOMICILIO"]);
                    _BEIngresoTRegistro.CODIGOCLASEPLANILLA = Convert.ToInt32(dr["CODIGOCLASEPLANILLA"]);
                    _BEIngresoTRegistro.FECHAINGRESO = Convert.ToDateTime(dr["FECHAINGRESO"]);
                    _BEIngresoTRegistro.IDCATEGORIASEMPLEADO = Convert.ToInt32(dr["IDCATEGORIASEMPLEADO"]);
                    _BEIngresoTRegistro.IDTIPOCONTRATO = Convert.ToString(dr["IDTIPOCONTRATO"]);
                    _BEIngresoTRegistro.SALARIORECIBO = Convert.ToDecimal(dr["SALARIORECIBO"]);

                }
                dr.Close();

                adoDataService.Dispose();

                return _BEIngresoTRegistro;
            }

        }

    }
}
